<?php
namespace Acme\Likipe\BlogBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Acme\Likipe\BlogBundle\Entity\Category;

/*
 * Acme\Likipe\BlogBundle\Repository\PostRepository
 */

class PostRepository extends EntityRepository{
	 
	public function findBySubCategory( $iCategoryId ){
		$oCategory = $this->getEntityManager()
			->getRepository( 'AcmeLikipeBlogBundle:Category' )
			->find( $iCategoryId );
		
		$aCategories = $this->getChildCategory( $oCategory );
			
		$aPost = array();
		
		foreach ( $aCategories as $oCategory ){
			$sQuery = $this->getEntityManager()->createQueryBuilder()
				->select( 'p' )
				->from( 'AcmeLikipeBlogBundle:Post_Category', 'p_c' )
				->from( 'AcmeLikipeBlogBundle:Post', 'p' )
				->where( 'p.id = p_c.postId' ) 
				->andWhere( 'p_c.categoryId = :cat_id' )
				->setParameter( 'cat_id', $oCategory->getId() )
				->getQuery();
			
			$aPost = array_merge( $aPost, $sQuery->getResult() );
		}
		
		return $aPost;
	}
	
	public function findTopComment( $iLimit ){
		$sQuery = $this->getEntityManager()->createQueryBuilder()
			->select( 'p.id', 'p.content', 'p.created', 'p.author', 
					'count(c.post) as countComment' )
			->from( 'AcmeLikipeBlogBundle:Comment', 'c' )
			->from( 'AcmeLikipeBlogBundle:Post', 'p' )
			->where( 'p.id = c.post' )
			->orderBy( 'countComment', 'DESC' )
			->groupBy( 'c.post' )
			->setFirstResult( 0 )
			->setMaxResults( $iLimit )
			->getQuery();
		
		return $sQuery->getResult();
	}
	
	public function getChildCategory( $oCategory ){
		$aCategories = $this->getEntityManager()
			->getRepository( 'AcmeLikipeBlogBundle:Category' )
			->findByParentCategory( $oCategory->getId() );
		
		foreach ( $aCategories as $oCatTemp ){
			$aCategories = array_merge( $aCategories, 
					$this->getChildCategory( $oCatTemp ));
		}
		
		$aCategories[] = $oCategory;
		
		return $aCategories;
	}
}
?>